package com.tanhua.common.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.common.pojo.TitleAndAmount;
import com.tanhua.common.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface UserInfoMapper extends BaseMapper<UserInfo> {

    //按年龄段分组查询
    @Select("SELECT age_info.title,COUNT(1) amount FROM \n" +
            "(SELECT *,\n" +
            "\tCASE\n" +
            "\tWHEN age BETWEEN 0 AND 17 THEN '0-17岁'\n" +
            "\tWHEN age BETWEEN 18 AND 23 THEN '18-23岁'\n" +
            "\tWHEN age BETWEEN 24 AND 30 THEN '24-30岁'\n" +
            "\tWHEN age BETWEEN 31 AND 40 THEN '31-40岁'\n" +
            "\tWHEN age BETWEEN 41 AND 50 THEN '41-50岁'\n" +
            "\tELSE '50岁+'\n" +
            "\tEND AS title FROM tb_user_info)\n" +
            "\tAS age_info\n" +
            "GROUP BY title")
    List<TitleAndAmount> selectAge();

    //按性别分组查询
    @Select("SELECT sex_info.title,COUNT(1) amount FROM \n" +
            "(SELECT *,\n" +
            "\tCASE\n" +
            "\tWHEN sex = 1 THEN '男'\n" +
            "\tWHEN sex = 2 THEN '女'\n" +
            "\tELSE '未知'\n" +
            "\tEND AS title FROM tb_user_info)\n" +
            "\tAS sex_info\n" +
//            "WHERE created BETWEEN #{sdDate} AND #{edDate} \n" +
            "GROUP BY title")
//    List<TitleAndAmount> selectSex(@Param("sdDate")Date sdDate,@Param("edDate")Date edDate);
    List<TitleAndAmount> selectSex();

    //按城市分组查询
    @Select("SELECT city_info.title,COUNT(1) amount FROM \n" +
            "(SELECT *,\n" +
            "\tCASE\n" +
            "\tWHEN city LIKE '北京%' THEN '北京'\n" +
            "\tWHEN city LIKE '天津%' THEN '天津'\n" +
            "\tWHEN city LIKE '河北%' THEN '河北'\n" +
            "\tWHEN city LIKE '山西%' THEN '山西'\n" +
            "\tWHEN city LIKE '内蒙古%' THEN '内蒙古'\n" +
            "\tWHEN city LIKE '黑龙江%' THEN '黑龙江'\n" +
            "\tWHEN city LIKE '吉林%' THEN '吉林'\n" +
            "\tWHEN city LIKE '辽宁%' THEN '辽宁'\n" +
            "\tWHEN city LIKE '上海%' THEN '上海'\n" +
            "\tWHEN city LIKE '江苏%' THEN '江苏'\n" +
            "\tWHEN city LIKE '浙江%' THEN '浙江'\n" +
            "\tWHEN city LIKE '安徽%' THEN '安徽'\n" +
            "\tWHEN city LIKE '江西%' THEN '江西'\n" +
            "\tWHEN city LIKE '山东%' THEN '山东'\n" +
            "\tWHEN city LIKE '福建%' THEN '福建'\n" +
            "\tWHEN city LIKE '台湾%' THEN '台湾'\n" +
            "\tWHEN city LIKE '河南%' THEN '河南'\n" +
            "\tWHEN city LIKE '湖北%' THEN '湖北'\n" +
            "\tWHEN city LIKE '湖南%' THEN '湖南'\n" +
            "\tWHEN city LIKE '广东%' THEN '广东'\n" +
            "\tWHEN city LIKE '广西%' THEN '广西'\n" +
            "\tWHEN city LIKE '海南%' THEN '海南'\n" +
            "\tWHEN city LIKE '香港%' THEN '香港'\n" +
            "\tWHEN city LIKE '澳门%' THEN '澳门'\n" +
            "\tWHEN city LIKE '重庆%' THEN '重庆'\n" +
            "\tWHEN city LIKE '四川%' THEN '四川'\n" +
            "\tWHEN city LIKE '贵州%' THEN '贵州'\n" +
            "\tWHEN city LIKE '云南%' THEN '云南'\n" +
            "\tWHEN city LIKE '西藏%' THEN '西藏'\n" +
            "\tWHEN city LIKE '陕西%' THEN '陕西'\n" +
            "\tWHEN city LIKE '甘肃%' THEN '甘肃'\n" +
            "\tWHEN city LIKE '青海%' THEN '青海'\n" +
            "\tWHEN city LIKE '宁夏%' THEN '宁夏'\n" +
            "\tWHEN city LIKE '新疆%' THEN '新疆'\n" +
            "\tELSE '未知'\n" +
            "\tEND AS title FROM tb_user_info)\n" +
            "\tAS city_info\n" +
            "GROUP BY title\n" +
            "ORDER BY amount DESC\n" +
            "LIMIT 0,10")
    List<TitleAndAmount> selectCity();

    @Select("SELECT industry_info.title,COUNT(1) amount FROM \n" +
            "(SELECT *,\n" +
            "\tCASE\n" +
            "\tWHEN industry LIKE '计算机%' THEN '计算机'\n" +
            "\tWHEN industry LIKE '制造%' THEN '制造'\n" +
            "\tWHEN industry LIKE '服务%' THEN '服务'\n" +
            "\tWHEN industry LIKE '地产%' THEN '地产'\n" +
            "\tWHEN industry LIKE '住宿%' THEN '住宿'\n" +
            "\tWHEN industry LIKE '教育%' THEN '教育'\n" +
            "\tWHEN industry LIKE '餐饮%' THEN '餐饮'\n" +
            "\tELSE '未知'\n" +
            "\tEND AS title FROM tb_user_info)\n" +
            "\tAS industry_info\n" +
            "GROUP BY title")
    List<TitleAndAmount> selectIndustry();
}